const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);

let connectDAO = require("./connect.js");

// 获取违规列表
router.get("/violation_list", function (req, res) {
  // 解析参数并设置默认值
  const { page = 1, pageSize = 10, Category } = req.query;

  // 参数验证
  let pageNum = parseInt(page);
  let pageSizeNum = parseInt(pageSize);
  if (isNaN(pageNum)) pageNum = 1;
  if (isNaN(pageSizeNum)) pageSizeNum = 10;
  const offset = (pageNum - 1) * pageSizeNum;
  // 构建基础SQL
  let baseSql = `SELECT * FROM Violation `;
  let countSql = `SELECT COUNT(*) AS total FROM Violation`;
  const conditions = [];

  // 筛选条件（保持您原有的拼接方式）
  if (Category) {
    conditions.push(`Category = '${Category}'`);
  }
  let whereClause = '';
  if (conditions.length > 0) {
    whereClause = ' WHERE ' + conditions.join(' AND ');
    baseSql += whereClause;
    countSql += whereClause;
  }
  // 分页查询
  const dataSql = `${baseSql} LIMIT ${pageSizeNum} OFFSET ${offset}`;


  // 先查询总数
  connectDAO.queryDataBySQL(countSql, (countResult) => {

    const total = countResult[0].total;

    // 再查询分页数据
    connectDAO.queryDataBySQL(dataSql, (dataResult) => {
      res.send({
        code: 200,
        msg: "获取成功",
        data: {
          records: dataResult,
          pagination: {
            current: pageNum,
            pageSize: pageSizeNum,
            total: total
          }
        }
      });
    });
  });
});

// 新增违规记录
router.post("/add_violation", function (req, res) {
  const { Category, UserID, UserName, ViolationTime, Description } = req.body;
  const sql = `INSERT INTO Violation (Category,UserID,UserName,ViolationTime,Description) VALUES 
  ('${Category}', '${UserID}', '${UserName}', '${ViolationTime}', '${Description}')`;
  connectDAO
    .queryDataBySQL(sql)
    .then(() => {
      res.send({ code: 200, msg: "Success" });
    })
});


// 更新违规
router.put("/update_violation", function (req, res) {
  const { Category, UserID, UserName, ViolationTime, Description, ViolationID } = req.body;
  const sql = `UPDATE Violation SET Category = '${Category}',UserID = '${UserID}',UserName = '${UserName}',ViolationTime = '${ViolationTime}',Description = '${Description}' WHERE ViolationID = ${ViolationID}`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.affectedRows > 0) {
      res.send({
        code: 200,
        msg: "Success",
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
      });
    }
  })
});

// 删除公告
router.delete("/delete_violation/:ViolationID", function (req, res) {
  const { ViolationID } = req.params;
  const sql = `DELETE FROM Violation WHERE ViolationID = ${ViolationID}`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.affectedRows > 0) {
      res.send({
        code: 200,
        msg: "Success"
      })
    }
  })
});

module.exports = router;
